﻿using System;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

namespace SDD
{
    public class NPOIComFunction
    {
        private HSSFWorkbook _workbook;
        private string _fullFilePath;
        private bool IsNewFile = false;
        private const string NewSheetName = "NewSheet";

        public HSSFWorkbook Workbook
        {
            get { return _workbook; }
            set { _workbook = value; }
        }
        public string FullFilePath
        {
            get { return _fullFilePath; }
            set { _fullFilePath = value; }
        }

        public NPOIComFunction()
        {
        }

        public NPOIComFunction(string FullFilePath)
        {
            this.FullFilePath = FullFilePath;
            InitializeWorkbook();
        }

        public void WriteToFile()
        {
            if (IsNewFile)
            {
                if (Workbook.GetSheetIndex(NewSheetName) > -1)
                {
                    Workbook.RemoveSheetAt(Workbook.GetSheetIndex(NewSheetName));
                }
            }
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(FullFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            Workbook.Write(file);
            Workbook = null;
            file.Close();
            file.Dispose();
        }

        public void InitializeWorkbook()
        {
            bool fileExists = true;
            FileInfo fi = new FileInfo(FullFilePath);
            fileExists = fi.Exists;
            fi = null;

            if (fileExists)
            {
                ExcelOpen();
            }
            else
            {
                ExcelCreate();
            }
        }

        public void ExcelOpen()
        {

            FileStream file = new FileStream(FullFilePath, FileMode.Open, FileAccess.ReadWrite);

            Workbook = new HSSFWorkbook(file);

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "洪明祁";
            Workbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "救國團施老師看盤班";
            si.Author = "洪明祁";
            Workbook.SummaryInformation = si;
            file.Close();
        }

        public void ExcelCreate()
        {
            Workbook = new HSSFWorkbook();
            IsNewFile = true;
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "洪明祁";
            Workbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "救國團施老師看盤班";
            Workbook.SummaryInformation = si;

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            //So we insert three sheet just like what Excel does
            Workbook.CreateSheet(NewSheetName);

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(FullFilePath, FileMode.Create);
            Workbook.Write(file);
            file.Close();


        }

        //
        public void ManageExcelFile(string destFilePath)
        {
            if (Workbook == null)
            {
                InitializeWorkbook();
            }

            int NumberOfSheets = Workbook.NumberOfSheets;
            int CutNum = NumberOfSheets / 30 - 1;

            if (NumberOfSheets < 100)
                return;
            else
            {
                File.Copy(FullFilePath, destFilePath, true);

                if (NumberOfSheets > 50)
                {
                    for (int i = 0; i < CutNum; i++)
                    {
                        Workbook.RemoveSheetAt(0);
                    }
                }

            }

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(FullFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            Workbook.Write(file);
            file.Close();
            file.Dispose();
            Workbook = null;
        }

    }
}
